package com.hjj.dao;

import com.hjj.beans.Stu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class StuDaoImp implements StuDao{
    @Override
    public ArrayList<Stu> getAll() throws SQLException {
        // 创建学生对象集合
        ArrayList<Stu> stus = new ArrayList<>();

        // 获取连接对象
        Connection cn = DB.open();

        // 创建sql语句
        String sql = "select * from stu1";

        // 获取称述对象
        PreparedStatement ps = cn.prepareStatement(sql);

        // 获取DQL结果集
        ResultSet rs = ps.executeQuery();

        // 遍历结果集
        while (rs.next()){
            Stu stu = new Stu();
            stu.setId(rs.getInt("id"));
            stu.setName(rs.getString("name"));
            stu.setAge(rs.getInt("age"));
            stu.setSex(rs.getString("sex"));
            stu.setScore(rs.getFloat("score"));

            stus.add(stu);
        }

        return stus;
    }

    @Override
    public Stu get(int id) throws SQLException {
        // 获取连接对象
        Connection cn = DB.open();

        // 创建sql语句
        String sql = "select * from stu1 where id = ?";

        // 获取称述对象
        PreparedStatement ps = cn.prepareStatement(sql);
        ps.setInt(1, id);

        // 获取DQL结果集
        ResultSet rs = ps.executeQuery();
        Stu stu = new Stu();
        // 遍历结果集
        while (rs.next()){
            stu.setId(rs.getInt("id"));
            stu.setName(rs.getString("name"));
            stu.setAge(rs.getInt("age"));
            stu.setSex(rs.getString("sex"));
            stu.setScore(rs.getFloat("score"));
        }

        return stu;
    }

    @Override
    public int add(Stu stu) throws SQLException {
        // 创建影响行数对象
        int row = 0;

        // 获取连接对象
        Connection cn = DB.open();

        // 创建sql语句
        String sql = "insert into stu1(name, age, sex, score) values (?, ?, ?, ?)";

        // 获取称述对象
        PreparedStatement ps = cn.prepareStatement(sql);
        ps.setString(1, stu.getName());
        ps.setInt(2, stu.getAge());
        ps.setString(3, stu.getSex());
        ps.setFloat(4, stu.getScore());

        // 获取DML返回的影响行数
        row = ps.executeUpdate();

        return row;
    }

    @Override
    public int edit(Stu stu) throws SQLException {
        // 创建影响行数对象
        int row = 0;

        // 获取连接对象
        Connection cn = DB.open();

        // 创建sql语句
        String sql = "update stu1 set name=?, age=?, sex=?, score=?  where id=?";

        // 获取称述对象
        PreparedStatement ps = cn.prepareStatement(sql);
        ps.setString(1, stu.getName());
        ps.setInt(2, stu.getAge());
        ps.setString(3, stu.getSex());
        ps.setFloat(4, stu.getScore());
        ps.setInt(5, stu.getId());

        // 获取返回的影响行数
        row = ps.executeUpdate();

        return row;
    }

    @Override
    public int del(int id) throws SQLException {
        // 创建影响行数对象
        int row = 0;

        // 获取连接对象
        Connection cn = DB.open();

        // 创建sql语句
        String sql = "delete from stu1 where id=?";

        // 获取称述对象
        PreparedStatement ps = cn.prepareStatement(sql);
        ps.setInt(1, id);

        // 获取返回的影响行数
        row = ps.executeUpdate();

        return row;
    }
}
